﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

// TODO:  Follow these steps to enable the Ribbon (XML) item:

// 1: Copy the following code block into the ThisAddin, ThisWorkbook, or ThisDocument class.

//  protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
//  {
//      return new Ribbon1();
//  }

// 2. Create callback methods in the "Ribbon Callbacks" region of this class to handle user
//    actions, such as clicking a button. Note: if you have exported this Ribbon from the Ribbon designer,
//    move your code from the event handlers to the callback methods and modify the code to work with the
//    Ribbon extensibility (RibbonX) programming model.

// 3. Assign attributes to the control tags in the Ribbon XML file to identify the appropriate callback methods in your code.  

// For more information, see the Ribbon XML documentation in the Visual Studio Tools for Office Help.


namespace QuickExcel
{
    [ComVisible(true)]
    public class Ribbon1 : Office.IRibbonExtensibility
    {
        private Office.IRibbonUI ribbon;
        

        public Ribbon1()
        {
        }

        #region IRibbonExtensibility Members

        public string GetCustomUI(string ribbonID)
        {
            return GetResourceText("QuickExcel.Ribbon1.xml");
        }

        #endregion

        #region Ribbon Callbacks
        //Create callback methods here. For more information about adding callback methods, select the Ribbon XML item in Solution Explorer and then press F1

        public void Ribbon_Load(Office.IRibbonUI ribbonUI)
        {
            this.ribbon = ribbonUI;
        }


        public void AlternateRowShading(Office.IRibbonControl control)
        {
            AlternateRowShading AlterRowShading = new AlternateRowShading();
            AlterRowShading.Show();
        }

        public void datePick(Office.IRibbonControl control)
        {
            DatePicker datepick = new DatePicker();
            datepick.Show();
        }

        public void UnitConversion(Office.IRibbonControl control)
        {
            UnitConverter unitConvert = new UnitConverter();
            unitConvert.Show();
        }

        public void CurrencyConversion(Office.IRibbonControl control)
        {
            CurrencyConverter currencyConvert = new CurrencyConverter();
            currencyConvert.Show();
        }

        public void SelectByVal(Office.IRibbonControl control)
        {
            SelectByValue SelByVal = new SelectByValue();
            SelByVal.Show();
        }

        public void SelectByFormat(Office.IRibbonControl control)
        {
            //Globals.ThisAddIn.Application.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory.ToString() + "\\FunctionLibrary.xlam");
            Globals.ThisAddIn.Application.SendKeys("^f");
        }

        public bool ModuleExist()
        {
            try
            {
                object QuickExcel_FuncMOd = Globals.ThisAddIn.Application.ActiveWorkbook.VBProject.VBComponents.Item("QuickExcelFunctions");
            }
            catch
            {
                return false;
            }
            return true;
        }


        public void ExportAllFunctions()
        {
            if (ModuleExist())
                return;

            Excel.Workbook wb;
            wb = Globals.ThisAddIn.Application.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory.ToString() + "\\FunctionLibrary.xlam");
            wb.VBProject.VBComponents.Item("QuickExcelFunctions").Export(wb.Path + "\\QuickExcelFunctions.bas");
            Globals.ThisAddIn.Application.ActiveWorkbook.VBProject.VBComponents.Import(wb.Path + "\\QuickExcelFunctions.bas");
            File.Delete(wb.Path + "\\QuickExcelFunctions.bas");
            //Globals.ThisAddIn.Application.Workbooks[wb.Name].Close(false);
        }
        public void QE_Functions(Office.IRibbonControl control)
        {
            ExportAllFunctions();

            Dictionary<string,string> funcNameNCall = new Dictionary<string,string>();

            funcNameNCall.Add("CELLCOLOR", "=CELLCOLOR(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("CELLHASFORMULA", "=CELLHASFORMULA(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("CELLTYPE", "=CELLTYPE(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("CHAR2", "=CHAR2(65)");
            funcNameNCall.Add("CODE2", "=CODE2(\"A\")");
            funcNameNCall.Add("CONTAINS", "=CONTAINS(\"text1\", \"text2\")");
            funcNameNCall.Add("COUNTAVISIBLE", "=COUNTAVISIBLE(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("COUNTBETWEEN", "=COUNTBETWEEN(" + Globals.ThisAddIn.Application.ActiveCell.Address + ", 1,2)");
            funcNameNCall.Add("CREDITCARD", "=CREDITCARD(45216546513)");
            funcNameNCall.Add("DAYSINMONTH", "=DAYSINMONTH(45632)");
            funcNameNCall.Add("DOLLARTEXT", "=DOLLARTEXT(1)");
            funcNameNCall.Add("EXCELDIR", "=EXCELDIR()");
            funcNameNCall.Add("EXTRACTELEMENT", "=EXTRACTELEMENT(\"txt,txt1\", 1, \",\")");
            funcNameNCall.Add("FILEEXISTS", "=FILEEXISTS(\"xyz.xlsx\")");
            funcNameNCall.Add("FILENAME", "=FILENAME()");
            funcNameNCall.Add("FONTCOLOR", "=FONTCOLOR(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("INSERTSTRING", "=INSERTSTRING(\"str\", \"String\", 2)");
            funcNameNCall.Add("ISLIKE", "=ISLIKE(\"text\", \"text\")");
            funcNameNCall.Add("LASTINCOLUMN", "=LASTINCOLUMN(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("LASTINROW", "=LASTINROW(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("MAXALLSHEETS", "=MAXALLSHEETS()");
            funcNameNCall.Add("MINALLSHEETS", "=MINALLSHEETS()");
            funcNameNCall.Add("MONTHWEEK", "=MONTHWEEK(45216)");
            funcNameNCall.Add("NUMBERFORMAT", "=NUMBERFORMAT(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("REMOVESPACES", "=REMOVESPACES(\"text  \")");
            funcNameNCall.Add("SayIt", "=SayIt(\"Hello\")");
            funcNameNCall.Add("SCRAMBLE", "=SCRAMBLE(\"text\")");
            funcNameNCall.Add("SELECTONE", "=SELECTONE(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("SHEETCOUNT", "=SHEETCOUNT()");
            funcNameNCall.Add("SHEETNAME", "=SHEETNAME()");
            funcNameNCall.Add("SHEETOFFSET", "=SHEETOFFSET(0)");
            funcNameNCall.Add("STATICRAND", "=STATICRAND()");
            funcNameNCall.Add("SUMALLSHEETS", "=SUMALLSHEETS()");
            funcNameNCall.Add("SUMVISIBLE", "=SUMVISIBLE(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("UNIQUEITEMS", "=UNIQUEITEMS(" + Globals.ThisAddIn.Application.ActiveCell.Address + ")");
            funcNameNCall.Add("USER", "=USER()");
            funcNameNCall.Add("WHICHDAY", "=WHICHDAY(1, 1, 1, 2004)");
            funcNameNCall.Add("XDATE", "=XDATE(2004, 1, 1)");
            funcNameNCall.Add("XDATEADD", "=XDATEADD(40562,263)");
            funcNameNCall.Add("XDATEDAY", "=XDATEDAY(40562)");
            funcNameNCall.Add("XDATEDIF", "=XDATEDIF(40562,40563)");
            funcNameNCall.Add("XDATEDOW", "=XDATEDOW(40562)");
            funcNameNCall.Add("XDATEMONTH", "=XDATEMONTH(40562)");
            funcNameNCall.Add("XDATEYEAR", "=XDATEYEAR(40562)");
            funcNameNCall.Add("XDATEYEARDIF", "=XDATEYEARDIF(40562, 40563)");

            Globals.ThisAddIn.Application.ActiveCell.Value = funcNameNCall[control.Id];
            Globals.ThisAddIn.Application.ActiveCell.FunctionWizard();
            Globals.ThisAddIn.Application.ActiveSheet.Calculate();
        }
        public void MinOrMax(Office.IRibbonControl control)
        {
            MinOrMaxVal MinMaxVal = new MinOrMaxVal();
            MinMaxVal.Show();
        }

        public void ExportRangetoFile(Office.IRibbonControl control)
        {
            ExportRangeToFile ExporttoFile = new ExportRangeToFile();
            ExporttoFile.Show();
        }

        public void ExportRangeAsGraphic(Office.IRibbonControl control)
        {
            ExportRangeAsGraphics ExportAsGraphics = new ExportRangeAsGraphics();
            ExportAsGraphics.Show();
        }

        public void ImportFileAtCursor(Office.IRibbonControl control)
        {
            ImportFile Import_file = new ImportFile();
            Import_file.Show();
        }

        public void createbookmark(Office.IRibbonControl control)
        {
            createBookmark Bookmark = new createBookmark();
            Bookmark.Show();
        }

        public void editbookmark(Office.IRibbonControl control)
        {
            editBookmark Bookmark = new editBookmark();
            Bookmark.Show();
        }

        public void applybookmark(Office.IRibbonControl control)
        {
            applyBookmarks Bookmark = new applyBookmarks();
            Bookmark.Show();
        }

        public void exportChart(Office.IRibbonControl control)
        {
            ExportChart export = new ExportChart();
            export.Show();
        }

        public void chartToPic(Office.IRibbonControl control)
        {
            if (Globals.ThisAddIn.Application.ActiveChart == null)
                System.Windows.Forms.MessageBox.Show("Please select the chart and Try Again");

            else
            {
                Globals.ThisAddIn.Application.ActiveChart.CopyPicture();
                Globals.ThisAddIn.Application.ActiveSheet.Paste();
            }
        }

        public void resizechart(Office.IRibbonControl control)
        {
            resizeChart resizechrt = new resizeChart();
            resizechrt.Show();
        }

        public void ToggleSettings(Office.IRibbonControl control)
        {
            toggleSetting toggle = new toggleSetting();
            toggle.Show();
        }

        public void sortsheet(Office.IRibbonControl control)
        {
            sortSheets sortsheets = new sortSheets();
            sortsheets.Show();
        }

        public void RibbonCallback(Office.IRibbonControl control)
        {
            if (control.Id == "About")
            {
                new AboutQuickExcel().Show();
            }

            else if (control.Id == "DeleteHyperlink")
            {
                Excel.Range Selection = Globals.ThisAddIn.Application.Selection as Excel.Range;
                
                    foreach(Excel.Range cell in Selection.Cells)
                    {
                        cell.Hyperlinks.Delete();
                    }
            }
            else if (control.Id == "InsertCalendarRange")
            {
                Excel.Worksheet sht = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet; //active sheet
                int row, col, ascii_value = 96;
                Excel.Range Selection = Globals.ThisAddIn.Application.Selection as Excel.Range;

                foreach (Excel.Range cell in Selection.Cells)
                {
                        cell.Value2 = DateTime.Now.ToShortDateString();
                        cell.NumberFormat = "mmm yyyy";
                    
                    row=Selection.Row;
                    col=Selection.Column;

                    Selection = sht.get_Range(Convert.ToChar(ascii_value + col) + "" + row , Convert.ToChar(ascii_value + col + 6) + "" + row); //add date as heading

                    Selection.Merge(); // merge the cells with heading
                    ((Excel.Range)cell).HorizontalAlignment = -4108;  // align the heading in center

                    Selection = sht.get_Range(Convert.ToChar(ascii_value + col) + "" + (row+1), Convert.ToChar(ascii_value + col + 6) + "" + (row+1)); // go to next row

                    int count = 1;

                    foreach (Excel.Range cell_4days in Selection.Cells)  // put days name in cells
                    {
                            cell_4days.Value2 = DayOfWeek.Sunday + count;
                            count += 1;

                            cell_4days.NumberFormat = "ddd";
                    }

                    DateTime a = Convert.ToDateTime("1/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString()); //find first date of the month

                    int num = Convert.ToInt32(a.DayOfWeek); //days of week to skip for first date

                    Selection = sht.get_Range(Convert.ToChar(ascii_value + col) + "" + (row + 1 + 1), Convert.ToChar(ascii_value + col + 6) + "" + (row + 7)); // range of cells where date to be put
                    
                    count = 0; //date count

                    foreach (Excel.Range cell_4days in Selection.Cells) // put date in cells
                    {

                        //System.Windows.Forms.MessageBox.Show(((Excel.Range)cell_4days).Column.ToString());


                        if (((Excel.Range)cell_4days).Column <= (((Excel.Range)cell).Column + num - 1) && ((Excel.Range)cell_4days).Row <= (((Excel.Range)cell).Row + 2) || a.AddDays(count).Month > a.Month) //condition for left the cells blank else put the date
                            continue;

                        cell_4days.Value2 = a.AddDays(count);
                        cell_4days.NumberFormat = "d";
                        
                        count += 1;                        
                    }

                    break;
                }

            }
        }
        
        #endregion

        #region Helpers

        private static string GetResourceText(string resourceName)
        {
            Assembly asm = Assembly.GetExecutingAssembly();
            String[] resourceNames = asm.GetManifestResourceNames();
            for (int i = 0; i < resourceNames.Length; ++i)
            {
                if (String.Compare(resourceName, resourceNames[i], StringComparison.OrdinalIgnoreCase) == 0)
                {
                    using (StreamReader resourceReader = new StreamReader(asm.GetManifestResourceStream(resourceNames[i])))
                    {
                        if (resourceReader != null)
                        {
                            return resourceReader.ReadToEnd();
                        }
                    }
                }
            }
            return null;
        }

        #endregion
    }
}
